﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runda.CMS.Common.Entity;
using Runda.CMS.Dal;
using Runda.CMS.Common.Utility;
using System.Transactions;
using System.Data;

namespace Runda.CMS.Bll
{
    public class BillBll:BllBase
    {
        //获取客户账龄信息
        public DataTable queryCustomerAgeInfo(string customerID)
        {
            string sql = @"select a.CustomerID,a.Age,a.Type from Cms_Arm_CustomerAge a
                        inner join 
                        (
                        select CustomerID,MAX(dateto) as DateTo from Cms_Arm_CustomerAge group by CustomerID
                        ) b on a.CustomerID= b.CustomerID and a.DateTo = b.DateTo
                    where a.CustomerID = '{0}' ";
	                
            sql = string.Format(sql, customerID);
            return new CommonDal().getSqlData(sql);
        }

        public DataTable QueryBill(string whereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QueryBill(whereClause, OrderBy, PageSize, PageIndex, out RecordCount);
        }

        /// <summary>
        /// 根据当前时间设置业务员当前月账单追回奖罚
        /// </summary>
        /// <param name="date"></param>
        public void SetBillPenaltyAwardForBusinessor(DateTime date) 
        {
            DataTable dt = new CtrtBillDal().QueryBill("");
            int irtn = new CtrtBillDal().DelBillPerformance(date.Year, date.Month);
            DataTable dt1;
            DataRow dr;

            for (int index = 0; index < dt.Rows.Count; index++) {
                decimal iNatSumTotal = System.Convert.ToDecimal(dt.Rows[index]["iNatSumTotal"]);
                decimal SubstrctTotal = System.Convert.ToDecimal(dt.Rows[index]["SubstrctTotal"]);
                decimal iExchSumTotal = System.Convert.ToDecimal(dt.Rows[index]["iExchSumTotal"]);
                string cBVCode = dt.Rows[index]["cBVCode"].ToString();
                string cCusCode = dt.Rows[index]["cCusCode"].ToString();
                DateTime dDate = System.Convert.ToDateTime(dt.Rows[index]["idDueDte"]);

                if (iNatSumTotal < 0) continue;
           
                //获取业务员信息,客户信息
                CtrtCustomerEntity customerEntity = new CtrtCustomerBll().Get<CtrtCustomerEntity>(" and CustomerCode = '" + cCusCode + "'");
                if (customerEntity == null) {
                    continue;
                }
                if (customerEntity.Salesman == null || customerEntity.Salesman == "") {
                    continue;
                }
                //本月奖励
                
                //ArmCustomerAgeEntity customerAgeEntity = new ArmCustomerAgeBll().Get<ArmCustomerAgeEntity>(" and CustomerID = '" + customerEntity.CustomerID + "' and DateFrom<='" + dDate.ToString()+"' and DateTo > '"+dDate.ToString()+"'");
                dt1 = queryCustomerAgeInfo(customerEntity.CustomerID.ToString());
                int customerAge = 0;
                int AgeType = 0;
                if (dt1.Rows.Count > 0 && dt1 != null)
                {
                    dr = dt1.Rows[0];

                    customerAge = Convert.ToInt16( dr["Age"]);
                    AgeType = Convert.ToInt16(dr["Type"]);
                }
                //if (customerAgeEntity != null) {
                //    customerAge = customerAgeEntity.Age;
                //}

                //到期日期
                DateTime dDateTerminate = new DateTime();
                //if (customerAgeEntity == null)
                if (customerAge==0)
                { dDateTerminate = dDate; }
                else
                {
                    //按月
                    //if (customerAgeEntity.Type == 0)
                    if (AgeType == 0)
                    {
                        dDateTerminate = new DateTime(dDate.AddMonths(customerAge + 1).Year, dDate.AddMonths(customerAge + 1).Month, 1).AddDays(-1);
                    }
                    //按天
                    //if (customerAgeEntity.Type == 1)
                    if (AgeType == 1)
                    {
                        //dDateTerminate = dDate.AddDays(customerAgeEntity.Age);
                        dDateTerminate = dDate.AddDays(customerAge);
                        
                    }
                }

                if (dDateTerminate.Day > dDate.Day) { dDateTerminate = dDateTerminate.AddDays(dDate.Day - dDateTerminate.Day); }
                               
                List<SysDictionaryEntity> dicEntitys = new List<SysDictionaryEntity>();
                ArmUserPerformanceEntity entity = new ArmUserPerformanceEntity();
                entity.cBVCode = cBVCode;
                entity.Month = date.Month;
                entity.Year = date.Year;
                entity.UserID = new Guid(customerEntity.Salesman);
                entity.ID = Guid.NewGuid();
                //如果没有到达到期日期计算累计收款奖励 date.CompareTo(dDateTerminate
                if ((date.Year < dDateTerminate.Year || (date.Year == dDateTerminate.Year && date.Month < dDateTerminate.Month)) && iExchSumTotal != 0)
                {
                    dicEntitys = new SysDictionaryBll().GetAll<SysDictionaryEntity>(" and Type = " + System.Convert.ToInt32(ContractEnumerator.FinianceAccountsReceivable.MonthRate) + " and BusinessType=1");
                    if (dicEntitys == null || dicEntitys.Count() == 0)
                    {
                        continue;
                    }
                    entity.Reward = iExchSumTotal * (dicEntitys[0].Value/100);
                }
                //如果到达到期日起计算收款惩罚
                if ((date.CompareTo(dDateTerminate) > 0 || (customerAge==0 && date.CompareTo(dDate) > 0)) && SubstrctTotal != 0)
                {                   
                    Int32 monthInteval = (dDateTerminate.Year - date.Year) * 12 + (dDateTerminate.Month - date.Month - 1);
                    //如果按天考量 未满一个月也需按一个月算
                    //if (customerAgeEntity != null)
                    if (customerAge != 0)
                    {
                        //if (customerAgeEntity.Type == 1)
                        if (AgeType == 1)
                        {
                            if (monthInteval == 0)
                            {
                                monthInteval = 1;
                            }
                        }
                    }
                    dicEntitys = new SysDictionaryBll().GetAll<SysDictionaryEntity>(" and Type = " + System.Convert.ToInt32(ContractEnumerator.FinianceAccountsReceivable.MonthRate) + " and BusinessType=0 and convert(int,Code) <= " + System.Math.Abs(monthInteval).ToString(), "order by Value Desc");
                    if (dicEntitys == null || dicEntitys.Count()==0) {
                        continue;
                    }
                    entity.Fine = SubstrctTotal * (dicEntitys[0].Value/100)*(-1);
                }
                if (entity.Reward != 0 || entity.Fine != 0) { new ArmUserPerformanceBll().Insert(entity); }
               
            }                  
        }

        public DataTable GetBills(string WhereClause,string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QueryBills(WhereClause,orderBy, PageSize, PageIndex, out RecordCount);
        }
        
        public DataTable GetCustomerBillForAgeStat(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().GetCustomerBillForAgeStat(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);
        }

        //获得销售发票
        public DataTable GetSalesBills(string WhereClause, string orderBy)
        {
            return new CtrtBillDal().QuerySalesBills(WhereClause, orderBy);
           
        }

        //获得销售发票
        public DataTable GetSalesBills(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QuerySalesBills(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);

        }

        //查询销售发票
        public DataTable QuerySalesBills(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QuerySalesBills1(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);

        }

        //删除发票
        

        public int DelSalesBill(string billno)
        {
            return new CtrtBillDal().DelBill(billno);

        }

        public int DelInstrumentCost(string sYear, string sMonth, string CompanyName, string instrumentCode)
        {
            return new CtrtBillDal().DelInstrumentCost(sYear,sMonth,instrumentCode);

        }

        public int DelInstrumentCost(string CompanyName, string instrumentCode)
        {
            return new CtrtBillDal().DelInstrumentCost(CompanyName, instrumentCode);

        }

        public int updateInstrumentAdjustCost(string instrumentCode, string sYear, string sMonth, decimal cost, string UpdatedMan)
        {
            return new CtrtBillDal().updateInstrumentAdjustCost(instrumentCode, sYear, sMonth, cost, UpdatedMan);

        }

        
        //获得销售发票 导出
        public DataTable GetSalesBills_export(string WhereClause)
        {
            return new CtrtBillDal().QuerySalesBills_export(WhereClause);
        }

        //获得销售发票 导出
        public DataTable GetSalesBills1_export(string WhereClause)
        {
            return new CtrtBillDal().QuerySalesBills1_export(WhereClause);
        }

        public DataTable GetSalesBills2_export(string WhereClause)
        {
            return new CtrtBillDal().QuerySalesBills2_export(WhereClause);
        }

        public DataTable CompanyInstrumentList_export(string WhereClause)
        {
            return new CtrtBillDal().CompanyInstrumentList_export( WhereClause);
        }

        public DataTable InstrumentList_export(string sYear,  string WhereClause)
        {
            return new CtrtBillDal().InstrumentList_export(sYear, WhereClause);
        }

        //预算关联 导出
        public DataTable GetAssociatedBudgetList_export(string WhereClause)
        {
            return new CtrtBillDal().QueryAssociatedBudget_export(WhereClause);
        }

        

        //获得发票的折扣总额
        public DataTable GetSalesZKBills(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().GetSalesZKBills(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);
        }

        public DataTable GetSalesZKBills_export(string WhereClause, string orderBy)
        {
            return new CtrtBillDal().GetSalesZKBills_export(WhereClause, orderBy);
        }
        //汇总发票的折扣总额 按月显示
        public DataTable SumSalesZK(string WhereClause)
        {
            return new CtrtBillDal().SumSalesZK(WhereClause);
        }
        

        //销售明细统计
        public DataTable SaleBillsStat(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().SaleBillsStat(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);
        }
        //销售明细统计 毛率
        public DataTable SaleBillsStat1(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().SaleBillsStat1(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);
        }

        //预算达成-按产品 当期
        public DataSet SalesRpt_102a(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select FactoryName,categorynameex
                 ,sum(isnull(budget.rdBudgetAmount,0)) as rdBudgetAmount,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount,case when sum(isnull(budget.rdBudgetAmount,0))=0 and  sum(isnull(sale.rdSaleAmount,0))!=0 then 1 when sum(isnull(budget.rdBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.rdSaleAmount,0)) / sum(budget.rdBudgetAmount),4) end as rdrate
                 ,sum(isnull(budget.kxBudgetAmount,0)) as kxBudgetAmount,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount,case when sum(isnull(budget.kxBudgetAmount,0))=0 and  sum(isnull(sale.kxSaleAmount,0))!=0 then 1 when sum(isnull(budget.kxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.kxSaleAmount,0)) / sum(budget.kxBudgetAmount),4) end as kxrate
                 ,sum(isnull(budget.yxBudgetAmount,0)) as yxBudgetAmount,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount,case when sum(isnull(budget.yxBudgetAmount,0))=0 and  sum(isnull(sale.yxSaleAmount,0))!=0 then 1 when sum(isnull(budget.yxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.yxSaleAmount,0)) / sum(budget.yxBudgetAmount),4) end as yxrate
                 ,sum(isnull(budget.hzBudgetAmount,0)) as hzBudgetAmount,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount,case when sum(isnull(budget.hzBudgetAmount,0))=0 and  sum(isnull(sale.hzSaleAmount,0))!=0 then 1 when sum(isnull(budget.hzBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hzSaleAmount,0)) / sum(budget.hzBudgetAmount),4) end as hzrate
                 ,sum(isnull(budget.hcBudgetAmount,0)) as hcBudgetAmount,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount,case when sum(isnull(budget.hcBudgetAmount,0))=0 and  sum(isnull(sale.hcSaleAmount,0))!=0 then 1 when sum(isnull(budget.hcBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hcSaleAmount,0)) / sum(budget.hcBudgetAmount),4) end as hcrate
                 ,sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)) as TotalBudgetAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 and sum(isnull(Sale.rdSaleAmount,0)+isnull(Sale.kxSaleAmount,0)+isnull(Sale.yxSaleAmount,0)+isnull(Sale.hzSaleAmount,0)+isnull(Sale.hcSaleAmount,0))!=0 then 1
                    when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 then 0
                 else round(sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)),4)
                 end as Totalrate
                from vw_InstrumentCategory_Stat cc
                left join 
                (
	                select categorycode
	                ,SUM(rdBudgetAmount) as rdBudgetAmount
	                ,SUM(kxBudgetAmount) as kxBudgetAmount
	                ,SUM(yxBudgetAmount) as yxBudgetAmount
	                ,SUM(hzBudgetAmount) as hzBudgetAmount
	                ,SUM(hcBudgetAmount) as hcBudgetAmount
	                from
	                (
	                select categorycode,BillMonth,companyname,sales
	                ,case when companyname='润达' then amount else 0 end as rdBudgetAmount
	                ,case when companyname='康祥' then amount else 0 end as kxBudgetAmount
	                ,case when companyname='益信' then amount else 0 end as yxBudgetAmount
	                ,case when companyname='惠中' then amount else 0 end as hzBudgetAmount
	                ,case when companyname='华臣' then amount else 0 end as hcBudgetAmount
	                from vw_SalesBudgets) s
                    where 1=1 {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) budget on cc.categoryCode = budget.categorycode 
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount) as rdSaleAmount
	                ,SUM(kxSaleAmount) as kxSaleAmount
	                ,SUM(yxSaleAmount) as yxSaleAmount
	                ,SUM(hzSaleAmount) as hzSaleAmount
	                ,SUM(hcSaleAmount) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,CustomerCode,companyname,sales
	                ,case when companyName='润达' then iNatSum else 0 end as rdSaleAmount
	                ,case when companyName='康祥' then iNatSum else 0 end as kxSaleAmount
	                ,case when companyName='益信' then iNatSum else 0 end as yxSaleAmount
	                ,case when companyName='惠中' then iNatSum else 0 end as hzSaleAmount
	                ,case when companyName='华臣' then iNatSum else 0 end as hcSaleAmount
	                from vw_BG_Bill) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                group by FactoryName,categorynameex";


            sql = string.Format(sql, sqlwhere, beginMonth,endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }
        //预算达成-按品牌 当期
        public DataSet SalesRpt_106a(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select FactoryName
                 ,sum(isnull(budget.rdBudgetAmount,0)) as rdBudgetAmount,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount,case when sum(isnull(budget.rdBudgetAmount,0))=0 and  sum(isnull(sale.rdSaleAmount,0))!=0 then 1 when sum(isnull(budget.rdBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.rdSaleAmount,0)) / sum(budget.rdBudgetAmount),4) end as rdrate
                 ,sum(isnull(budget.kxBudgetAmount,0)) as kxBudgetAmount,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount,case when sum(isnull(budget.kxBudgetAmount,0))=0 and  sum(isnull(sale.kxSaleAmount,0))!=0 then 1 when sum(isnull(budget.kxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.kxSaleAmount,0)) / sum(budget.kxBudgetAmount),4) end as kxrate
                 ,sum(isnull(budget.yxBudgetAmount,0)) as yxBudgetAmount,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount,case when sum(isnull(budget.yxBudgetAmount,0))=0 and  sum(isnull(sale.yxSaleAmount,0))!=0 then 1 when sum(isnull(budget.yxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.yxSaleAmount,0)) / sum(budget.yxBudgetAmount),4) end as yxrate
                 ,sum(isnull(budget.hzBudgetAmount,0)) as hzBudgetAmount,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount,case when sum(isnull(budget.hzBudgetAmount,0))=0 and  sum(isnull(sale.hzSaleAmount,0))!=0 then 1 when sum(isnull(budget.hzBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hzSaleAmount,0)) / sum(budget.hzBudgetAmount),4) end as hzrate
                 ,sum(isnull(budget.hcBudgetAmount,0)) as hcBudgetAmount,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount,case when sum(isnull(budget.hcBudgetAmount,0))=0 and  sum(isnull(sale.hcSaleAmount,0))!=0 then 1 when sum(isnull(budget.hcBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hcSaleAmount,0)) / sum(budget.hcBudgetAmount),4) end as hcrate
                 ,sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)) as TotalBudgetAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 and sum(isnull(Sale.rdSaleAmount,0)+isnull(Sale.kxSaleAmount,0)+isnull(Sale.yxSaleAmount,0)+isnull(Sale.hzSaleAmount,0)+isnull(Sale.hcSaleAmount,0))!=0 then 1
                    when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 then 0
                 else round(sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)),4)
                 end as Totalrate
                from vw_InstrumentCategory_Stat cc
                left join 
                (
	                select categorycode
	                ,SUM(rdBudgetAmount) as rdBudgetAmount
	                ,SUM(kxBudgetAmount) as kxBudgetAmount
	                ,SUM(yxBudgetAmount) as yxBudgetAmount
	                ,SUM(hzBudgetAmount) as hzBudgetAmount
	                ,SUM(hcBudgetAmount) as hcBudgetAmount
	                from
	                (
	                select categorycode,BillMonth,companyname,sales
	                ,case when companyname='润达' then amount else 0 end as rdBudgetAmount
	                ,case when companyname='康祥' then amount else 0 end as kxBudgetAmount
	                ,case when companyname='益信' then amount else 0 end as yxBudgetAmount
	                ,case when companyname='惠中' then amount else 0 end as hzBudgetAmount
	                ,case when companyname='华臣' then amount else 0 end as hcBudgetAmount
	                from vw_SalesBudgets) s
                    where 1=1 {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) budget on cc.categoryCode = budget.categorycode 
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount) as rdSaleAmount
	                ,SUM(kxSaleAmount) as kxSaleAmount
	                ,SUM(yxSaleAmount) as yxSaleAmount
	                ,SUM(hzSaleAmount) as hzSaleAmount
	                ,SUM(hcSaleAmount) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,CustomerCode,companyname,sales
	                ,case when companyName='润达' then iNatSum else 0 end as rdSaleAmount
	                ,case when companyName='康祥' then iNatSum else 0 end as kxSaleAmount
	                ,case when companyName='益信' then iNatSum else 0 end as yxSaleAmount
	                ,case when companyName='惠中' then iNatSum else 0 end as hzSaleAmount
	                ,case when companyName='华臣' then iNatSum else 0 end as hcSaleAmount
	                from vw_BG_Bill) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                group by FactoryName";


            sql = string.Format(sql, sqlwhere, beginMonth, endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }
        //预算达成-按产品 全年
        public DataSet SalesRpt_102b(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select FactoryName,categorynameex
                ,sum(isnull(budget.rdBudgetAmount,0)) as rdBudgetAmount,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount,case when sum(isnull(budget.rdBudgetAmount,0))=0 and  sum(isnull(sale.rdSaleAmount,0))!=0 then 1 when sum(isnull(budget.rdBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.rdSaleAmount,0)) / sum(budget.rdBudgetAmount),4) end as rdrate
                 ,sum(isnull(budget.kxBudgetAmount,0)) as kxBudgetAmount,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount,case when sum(isnull(budget.kxBudgetAmount,0))=0 and  sum(isnull(sale.kxSaleAmount,0))!=0 then 1 when sum(isnull(budget.kxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.kxSaleAmount,0)) / sum(budget.kxBudgetAmount),4) end as kxrate
                 ,sum(isnull(budget.yxBudgetAmount,0)) as yxBudgetAmount,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount,case when sum(isnull(budget.yxBudgetAmount,0))=0 and  sum(isnull(sale.yxSaleAmount,0))!=0 then 1 when sum(isnull(budget.yxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.yxSaleAmount,0)) / sum(budget.yxBudgetAmount),4) end as yxrate
                 ,sum(isnull(budget.hzBudgetAmount,0)) as hzBudgetAmount,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount,case when sum(isnull(budget.hzBudgetAmount,0))=0 and  sum(isnull(sale.hzSaleAmount,0))!=0 then 1 when sum(isnull(budget.hzBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hzSaleAmount,0)) / sum(budget.hzBudgetAmount),4) end as hzrate
                 ,sum(isnull(budget.hcBudgetAmount,0)) as hcBudgetAmount,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount,case when sum(isnull(budget.hcBudgetAmount,0))=0 and  sum(isnull(sale.hcSaleAmount,0))!=0 then 1 when sum(isnull(budget.hcBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hcSaleAmount,0)) / sum(budget.hcBudgetAmount),4) end as hcrate
                 ,sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)) as TotalBudgetAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 and sum(isnull(Sale.rdSaleAmount,0)+isnull(Sale.kxSaleAmount,0)+isnull(Sale.yxSaleAmount,0)+isnull(Sale.hzSaleAmount,0)+isnull(Sale.hcSaleAmount,0))!=0 then 1
                    when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 then 0
                 else round(sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)),4)
                 end as Totalrate
                from vw_InstrumentCategory_Stat cc
                left join 
                (
	                select categorycode
	                ,SUM(rdBudgetAmount) as rdBudgetAmount
	                ,SUM(kxBudgetAmount) as kxBudgetAmount
	                ,SUM(yxBudgetAmount) as yxBudgetAmount
	                ,SUM(hzBudgetAmount) as hzBudgetAmount
	                ,SUM(hcBudgetAmount) as hcBudgetAmount
	                from
	                (
	                select categorycode,BillMonth,companyname,sales
	                ,case when companyname='润达' then amount else 0 end as rdBudgetAmount
	                ,case when companyname='康祥' then amount else 0 end as kxBudgetAmount
	                ,case when companyname='益信' then amount else 0 end as yxBudgetAmount
	                ,case when companyname='惠中' then amount else 0 end as hzBudgetAmount
	                ,case when companyname='华臣' then amount else 0 end as hcBudgetAmount
	                from vw_SalesBudgets) s
                    where 1=1 {0} and left(BillMonth,4) = left('{1}',4)
	                group by categorycode
                ) budget on cc.categoryCode = budget.categorycode 
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount) as rdSaleAmount
	                ,SUM(kxSaleAmount) as kxSaleAmount
	                ,SUM(yxSaleAmount) as yxSaleAmount
	                ,SUM(hzSaleAmount) as hzSaleAmount
	                ,SUM(hcSaleAmount) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,CustomerCode,companyname,sales
	                ,case when companyName='润达' then iNatSum else 0 end as rdSaleAmount
	                ,case when companyName='康祥' then iNatSum else 0 end as kxSaleAmount
	                ,case when companyName='益信' then iNatSum else 0 end as yxSaleAmount
	                ,case when companyName='惠中' then iNatSum else 0 end as hzSaleAmount
	                ,case when companyName='华臣' then iNatSum else 0 end as hcSaleAmount
	                from vw_BG_Bill) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                group by FactoryName,categorynameex";
            sql = string.Format(sql, sqlwhere, beginMonth, endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }
        //预算达成-按品牌 全年
        public DataSet SalesRpt_106b(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select FactoryName
                 ,sum(budget.rdBudgetAmount) as rdBudgetAmount,sum(sale.rdSaleAmount) as rdSaleAmount,case when sum(isnull(budget.rdBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.rdSaleAmount,0)) / sum(budget.rdBudgetAmount),4) end as rdrate
                 ,sum(budget.kxBudgetAmount) as kxBudgetAmount,sum(sale.kxSaleAmount) as kxSaleAmount,case when sum(isnull(budget.kxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.kxSaleAmount,0)) / sum(budget.kxBudgetAmount),4) end as kxrate
                 ,sum(budget.yxBudgetAmount) as yxBudgetAmount,sum(sale.yxSaleAmount) as yxSaleAmount,case when sum(isnull(budget.yxBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.yxSaleAmount,0)) / sum(budget.yxBudgetAmount),4) end as yxrate
                 ,sum(budget.hzBudgetAmount) as hzBudgetAmount,sum(sale.hzSaleAmount) as hzSaleAmount,case when sum(isnull(budget.hzBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hzSaleAmount,0)) / sum(budget.hzBudgetAmount),4) end as hzrate
                 ,sum(budget.hcBudgetAmount) as hcBudgetAmount,sum(sale.hcSaleAmount) as hcSaleAmount,case when sum(isnull(budget.hcBudgetAmount,0))=0 then 0 else ROUND(sum(isnull(sale.hcSaleAmount,0)) / sum(budget.hcBudgetAmount),4) end as hcrate
                 ,sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)) as TotalBudgetAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0))=0 then 0
                 else round(sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) / sum(isnull(budget.rdBudgetAmount,0)+isnull(budget.kxBudgetAmount,0)+isnull(budget.yxBudgetAmount,0)+isnull(budget.hzBudgetAmount,0)+isnull(budget.hcBudgetAmount,0)),4)
                 end as Totalrate
                from vw_InstrumentCategory_Stat cc
                left join 
                (
	                select categorycode
	                ,SUM(rdBudgetAmount) as rdBudgetAmount
	                ,SUM(kxBudgetAmount) as kxBudgetAmount
	                ,SUM(yxBudgetAmount) as yxBudgetAmount
	                ,SUM(hzBudgetAmount) as hzBudgetAmount
	                ,SUM(hcBudgetAmount) as hcBudgetAmount
	                from
	                (
	                select categorycode,BillMonth,companyname,sales
	                ,case when companyname='润达' then amount else 0 end as rdBudgetAmount
	                ,case when companyname='康祥' then amount else 0 end as kxBudgetAmount
	                ,case when companyname='益信' then amount else 0 end as yxBudgetAmount
	                ,case when companyname='惠中' then amount else 0 end as hzBudgetAmount
	                ,case when companyname='华臣' then amount else 0 end as hcBudgetAmount
	                from vw_SalesBudgets) s
                    where 1=1 {0} and left(BillMonth,4) = left('{1}',4)
	                group by categorycode
                ) budget on cc.categoryCode = budget.categorycode 
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount) as rdSaleAmount
	                ,SUM(kxSaleAmount) as kxSaleAmount
	                ,SUM(yxSaleAmount) as yxSaleAmount
	                ,SUM(hzSaleAmount) as hzSaleAmount
	                ,SUM(hcSaleAmount) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,CustomerCode,companyname,sales
	                ,case when companyName='润达' then iNatSum else 0 end as rdSaleAmount
	                ,case when companyName='康祥' then iNatSum else 0 end as kxSaleAmount
	                ,case when companyName='益信' then iNatSum else 0 end as yxSaleAmount
	                ,case when companyName='惠中' then iNatSum else 0 end as hzSaleAmount
	                ,case when companyName='华臣' then iNatSum else 0 end as hcSaleAmount
	                from vw_BG_Bill) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                group by FactoryName";
            sql = string.Format(sql, sqlwhere, beginMonth, endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }
        //预算达成-按产品 当期 汇总
        public DataTable sumSalesRpt_102a(string sqlwhere,string strCondition, string beginMonth, string endMonth)
        {
            return new CtrtBillDal().sumSalesRpt_102a(sqlwhere, strCondition, beginMonth, endMonth);
        }
        //销售模式-产品 当期 汇总
        public DataTable sumSalesRpt_108(string sqlwhere, string strCondition, string beginMonth, string endMonth)
        {
            return new CtrtBillDal().sumSalesRpt_108(sqlwhere, strCondition, beginMonth, endMonth);
        }
        //预算达成-按产品 全年 汇总
        public DataTable sumSalesRpt_102b(string sqlwhere, string strCondition, string beginMonth, string endMonth,string strYear)
        {
            return new CtrtBillDal().sumSalesRpt_102b(sqlwhere, strCondition, beginMonth, endMonth,strYear);
        }

        #region 销售利润分析
        //品牌存货大类
        public DataSet SalesRpt_E1(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select
        s1.*
        ,s1.rdSaleAmount+s1.kxSaleAmount+s1.yxSaleAmount+s1.hzSaleAmount+s1.hcSaleAmount as coSaleAmoumt
        ,s1.rdCost+s1.kxCost+s1.yxCost+s1.hzCost+s1.hcCost as coCost
        ,s1.rdRevenue+s1.kxRevenue+s1.yxRevenue+s1.hzRevenue+s1.hcRevenue as coRevenue
        ,case when s1.rdSaleAmount!=0 then s1.rdRevenue / s1.rdSaleAmount else 0 end as rdRevenueRate
        ,case when s1.kxSaleAmount!=0 then s1.kxRevenue / s1.kxSaleAmount else 0 end as kxRevenueRate
        ,case when s1.yxSaleAmount!=0 then s1.yxRevenue / s1.yxSaleAmount else 0 end as yxRevenueRate
        ,case when s1.hzSaleAmount!=0 then s1.hzRevenue / s1.hzSaleAmount else 0 end as hzRevenueRate
        ,case when s1.hcSaleAmount!=0 then s1.hcRevenue / s1.hcSaleAmount else 0 end as hcRevenueRate
        ,case when (s1.rdSaleAmount+s1.kxSaleAmount+s1.yxSaleAmount+s1.hzSaleAmount+s1.hcSaleAmount)!=0 then (s1.rdRevenue+s1.kxRevenue+s1.yxRevenue+s1.hzRevenue+s1.hcRevenue) / (s1.rdSaleAmount+s1.kxSaleAmount+s1.yxSaleAmount+s1.hzSaleAmount+s1.hcSaleAmount) else 0 end as coRevenueRate
        from
        (
        select 
	        FactoryName,categorynameex
	        ,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount,sum(isnull(Cost.rdCost,0)) as rdCost,sum(isnull(sale.rdSaleAmount,0)) - sum(isnull(Cost.rdCost,0)) as rdRevenue
	        ,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount,sum(isnull(Cost.kxCost,0)) as kxCost,sum(isnull(sale.kxSaleAmount,0)) - sum(isnull(Cost.kxCost,0)) as kxRevenue
	        ,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount,sum(isnull(Cost.yxCost,0)) as yxCost,sum(isnull(sale.yxSaleAmount,0)) - sum(isnull(Cost.yxCost,0)) as yxRevenue
	        ,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount,sum(isnull(Cost.hzCost,0)) as hzCost,sum(isnull(sale.hzSaleAmount,0)) - sum(isnull(Cost.hzCost,0)) as hzRevenue
	        ,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount,sum(isnull(Cost.hcCost,0)) as hcCost,sum(isnull(sale.hcSaleAmount,0)) - sum(isnull(Cost.hcCost,0)) as hcRevenue
            from vw_InstrumentCategory_StatEx cc
            left join 
            (
                select categorycode
			        ,SUM(rdSaleAmount) as rdSaleAmount
			        ,SUM(kxSaleAmount) as kxSaleAmount
			        ,SUM(yxSaleAmount) as yxSaleAmount
			        ,SUM(hzSaleAmount) as hzSaleAmount
			        ,SUM(hcSaleAmount) as hcSaleAmount
			        from
			        (
				        select categorycode,BillMonth,CustomerCode,companyname,sales
				        ,case when companyName='润达' then saleAmount+saleAmount1+saleAmount2+saleAmount3+saleAmount4 else 0 end as rdSaleAmount
				        ,case when companyName='康祥' then saleAmount+saleAmount1+saleAmount2+saleAmount3+saleAmount4 else 0 end as kxSaleAmount
				        ,case when companyName='益信' then saleAmount+saleAmount1+saleAmount2+saleAmount3+saleAmount4 else 0 end as yxSaleAmount
				        ,case when companyName='惠中' then saleAmount+saleAmount1+saleAmount2+saleAmount3+saleAmount4 else 0 end as hzSaleAmount
				        ,case when companyName='华臣' then saleAmount+saleAmount1+saleAmount2+saleAmount3+saleAmount4 else 0 end as hcSaleAmount
				        from vw_BG_Bill

			        ) s
		        where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
		        group by categorycode
            ) Sale on cc.categoryCode = sale.categorycode 
            left join
            (	
                select categorycode
			        ,SUM(rdCost) as rdCost
			        ,SUM(kxCost) as kxCost
			        ,SUM(yxCost) as yxCost
			        ,SUM(hzCost) as hzCost
			        ,SUM(hcCost) as hcCost
			        from
			        (
				        select categorycode,BillMonth,CustomerCode,companyname,sales
				        ,case when companyName='润达' then Cost+Cost2+Cost3 else 0 end as rdCost
				        ,case when companyName='康祥' then Cost+Cost2+Cost3 else 0 end as kxCost
				        ,case when companyName='益信' then Cost+Cost2+Cost3 else 0 end as yxCost
				        ,case when companyName='惠中' then Cost+Cost2+Cost3 else 0 end as hzCost
				        ,case when companyName='华臣' then Cost+Cost2+Cost3 else 0 end as hcCost
				        from vw_BG_Bill
        			    
				        ) s
			        where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
			        group by categorycode
            ) Cost on Cost.categorycode = cc.categoryCode
            group by FactoryName,categorynameex
         ) s1 order by FactoryName,categorynameex";


            sql = string.Format(sql, sqlwhere, beginMonth, endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }
        #endregion
        //销售明细统计-按产品线 new
        public DataSet SalesRpt_103(string sqlwhere)
        {
            string sql = @"select 
                    FactoryName,categoryNameex
                    ,SUM(Mon1Natsum) as Mon1Natsum
                    ,sum(Mon2Natsum) as Mon2Natsum
                    ,sum(Mon3Natsum) as Mon3Natsum
                    ,sum(Mon4Natsum) as Mon4Natsum
                    ,sum(Mon5Natsum) as Mon5Natsum
                    ,sum(Mon6Natsum) as Mon6Natsum
                    ,sum(Mon7Natsum) as Mon7Natsum
                    ,sum(Mon8Natsum) as Mon8Natsum
                    ,sum(Mon9Natsum) as Mon9Natsum
                    ,sum(Mon10Natsum) as Mon10Natsum
                    ,sum(Mon11Natsum) as Mon11Natsum
                    ,sum(Mon12Natsum) as Mon12Natsum
                    ,(sum(ISNULL(Mon1Natsum,0))+sum(ISNULL(Mon2Natsum,0))+sum(ISNULL(Mon3Natsum,0))+sum(ISNULL(Mon4Natsum,0))
                    +sum(ISNULL(Mon5Natsum,0))+sum(ISNULL(Mon6Natsum,0))+sum(ISNULL(Mon7Natsum,0))+sum(ISNULL(Mon8Natsum,0))
                    +sum(ISNULL(Mon9Natsum,0))+sum(ISNULL(Mon10Natsum,0))+sum(ISNULL(Mon11Natsum,0))+sum(ISNULL(Mon12Natsum,0))) as TotalNatsum
                    from
                    (
                    select
                    a.FactoryName,a.categoryNameex,b.* from
                    (
                     select FactoryName,categoryNameex,categoryCode from vw_InstrumentCategory_Stat
                     ) a
                     left join
                    (
                    select CategoryCode
                    ,sum(Mon1Natsum) as Mon1Natsum
                    ,sum(Mon2Natsum) as Mon2Natsum
                    ,sum(Mon3Natsum) as Mon3Natsum
                    ,sum(Mon4Natsum) as Mon4Natsum
                    ,sum(Mon5Natsum) as Mon5Natsum
                    ,sum(Mon6Natsum) as Mon6Natsum
                    ,sum(Mon7Natsum) as Mon7Natsum
                    ,sum(Mon8Natsum) as Mon8Natsum
                    ,sum(Mon9Natsum) as Mon9Natsum
                    ,sum(Mon10Natsum) as Mon10Natsum
                    ,sum(Mon11Natsum) as Mon11Natsum
                    ,sum(Mon12Natsum) as Mon12Natsum
                     from vw_SaleBills_1
                     where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0}
                     group by CategoryCode
                     ) b on a.categoryCode = b.categorycode
                    ) s
                    group by FactoryName,categoryNameex";
            sql = string.Format(sql, sqlwhere);
            return new CommonDal().getSqlData_DS(sql);
        }

        //销售明细统计-按产品 全年 汇总
        public DataTable sumSalesRpt_103(string sqlwhere, string strCondition)
        {
            return new CtrtBillDal().sumSalesRpt_103(sqlwhere, strCondition);
        }

        //上年同期对比
        public DataSet SalesRpt_104(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select FactoryName,categorynameex
                     ,sum(isnull(presales.rdpresalesAmount,0)) as rdpresalesAmount,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount,case when sum(isnull(presales.rdpresalesAmount,0))=0 and  sum(isnull(sale.rdSaleAmount,0))!=0 then 1 when sum(isnull(presales.rdpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.rdSaleAmount,0))-sum(isnull(presales.rdpresalesAmount,0))) / sum(presales.rdpresalesAmount),4) end as rdrate
                 ,sum(isnull(presales.kxpresalesAmount,0)) as kxpresalesAmount,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount,case when sum(isnull(presales.kxpresalesAmount,0))=0 and  sum(isnull(sale.kxSaleAmount,0))!=0 then 1 when sum(isnull(presales.kxpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.kxSaleAmount,0))-sum(isnull(presales.kxpresalesAmount,0))) / sum(presales.kxpresalesAmount),4) end as kxrate
                 ,sum(isnull(presales.yxpresalesAmount,0)) as yxpresalesAmount,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount,case when sum(isnull(presales.yxpresalesAmount,0))=0 and  sum(isnull(sale.yxSaleAmount,0))!=0 then 1 when sum(isnull(presales.yxpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.yxSaleAmount,0))-sum(isnull(presales.yxpresalesAmount,0))) / sum(presales.yxpresalesAmount),4) end as yxrate
                 ,sum(isnull(presales.hzpresalesAmount,0)) as hzpresalesAmount,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount,case when sum(isnull(presales.hzpresalesAmount,0))=0 and  sum(isnull(sale.hzSaleAmount,0))!=0 then 1 when sum(isnull(presales.hzpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.hzSaleAmount,0))-sum(isnull(presales.hzpresalesAmount,0))) / sum(presales.hzpresalesAmount),4) end as hzrate
                 ,sum(isnull(presales.hcpresalesAmount,0)) as hcpresalesAmount,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount,case when sum(isnull(presales.hcpresalesAmount,0))=0 and  sum(isnull(sale.hcSaleAmount,0))!=0 then 1 when sum(isnull(presales.hcpresalesAmount,0))=0 then 0 else ROUND((sum(isnull(sale.hcSaleAmount,0))-sum(isnull(presales.hcpresalesAmount,0))) / sum(presales.hcpresalesAmount),4) end as hcrate
                 ,sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0)) as TotalpresalesAmount
                 ,sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                 ,case when sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0))=0 and sum(isnull(Sale.rdSaleAmount,0)+isnull(Sale.kxSaleAmount,0)+isnull(Sale.yxSaleAmount,0)+isnull(Sale.hzSaleAmount,0)+isnull(Sale.hcSaleAmount,0))!=0 then 1
                    when sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0))=0 then 0
                 else round((sum(isnull(sale.rdSaleAmount,0)+isnull(sale.kxSaleAmount,0)+isnull(sale.yxSaleAmount,0)+isnull(sale.hzSaleAmount,0)+isnull(sale.hcSaleAmount,0))-sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0))) / sum(isnull(presales.rdpresalesAmount,0)+isnull(presales.kxpresalesAmount,0)+isnull(presales.yxpresalesAmount,0)+isnull(presales.hzpresalesAmount,0)+isnull(presales.hcpresalesAmount,0)),4)
                 end as Totalrate
                    from vw_InstrumentCategory_Stat_ex cc
                    left join 
                    (
	                    select categorycode
	                    ,SUM(rdpresalesamount) as rdpresalesamount
	                    ,SUM(kxpresalesamount) as kxpresalesamount
	                    ,SUM(yxpresalesamount) as yxpresalesamount
	                    ,SUM(hzpresalesamount) as hzpresalesamount
	                    ,SUM(hcpresalesamount) as hcpresalesamount
	                    from
	                    (
	                    select categorycode,sMonth,companyname
	                    ,case when companyname='润达' then salesamount else 0 end as rdpresalesamount
	                    ,case when companyname='康祥' then salesamount else 0 end as kxpresalesamount
	                    ,case when companyname='益信' then salesamount else 0 end as yxpresalesamount
	                    ,case when companyname='惠中' then salesamount else 0 end as hzpresalesamount
	                    ,case when companyname='华臣' then salesamount else 0 end as hcpresalesamount
	                    from cms_bg_salebills_2011) s
                    where 1=1 {0}  and sMonth >= substring('{1}',6,2) and sMonth <= substring('{2}',6,2)
	                group by categorycode
                ) presales on cc.categorycode = presales.categorycode 
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount) as rdSaleAmount
	                ,SUM(kxSaleAmount) as kxSaleAmount
	                ,SUM(yxSaleAmount) as yxSaleAmount
	                ,SUM(hzSaleAmount) as hzSaleAmount
	                ,SUM(hcSaleAmount) as hcSaleAmount
	                from
	                (
	                select categorycode,BillMonth,customercode,companyname
	                ,case when companyname='润达' then iNatSum else 0 end as rdSaleAmount
	                ,case when companyname='康祥' then iNatSum else 0 end as kxSaleAmount
	                ,case when companyname='益信' then iNatSum else 0 end as yxSaleAmount
	                ,case when companyname='惠中' then iNatSum else 0 end as hzSaleAmount
	                ,case when companyname='华臣' then iNatSum else 0 end as hcSaleAmount
	                from vw_SalesBills_rpt) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                group by FactoryName,categorynameex";

            sql = string.Format(sql, sqlwhere, beginMonth, endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }
        //上年同期对比 汇总
        public DataTable sumSalesRpt_104(string sqlwhere, string strCondition, string beginMonth, string endMonth)
        {
            return new CtrtBillDal().sumSalesRpt_104(sqlwhere, strCondition, beginMonth, endMonth);
        }

        //小组预算达成
        public DataSet SalesRpt_105(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select a.DepartmentName, a.sales,cate.FactoryName,cate.categoryNameex,isnull(b.BudgetAmount,0) as BudgetAmount,isnull(c.SalesAmount,0) as SalesAmount 
           ,case when isnull(b.BudgetAmount,0)=0 and isnull(c.SalesAmount,0)!=0 then 1 when isnull(b.BudgetAmount,0)=0 then 0 else ROUND(isnull(c.SalesAmount,0) / b.BudgetAmount,4) end as YearRate
            ,isnull(b1.BudgetAmount1,0) as BudgetAmount1,isnull(c1.SalesAmount1,0) as SalesAmount1
            ,case when isnull(b1.BudgetAmount1,0)=0 and isnull(c1.SalesAmount1,0)!=0 then 1 when isnull(b1.BudgetAmount1,0)=0 then 0 else ROUND(isnull(c1.SalesAmount1,0) / b1.BudgetAmount1,4) end as CurRate
            from
            (
	            select sales,CategoryCode,DepartmentName
            from vw_SaleBuget_SalesCategoryCode with(nolock)
            ) a
            --left join vw_sysUser u on u.UserName = a.sales
            left join Cms_Ctrt_InstrumentCategory cate with(nolock) on cate.categoryCode = a.CategoryCode
            left join
            (
            select departmentName,sales,CategoryCode,SUM(Amount) as BudgetAmount from vw_Cms_BG_SalesBudgets_ex
            where 1=1 {0} and left(BudgetMonth,4) = left( '{1}',4)
            group by departmentName,sales,CategoryCode
            ) b on a.sales = b.sales and a.CategoryCode = b.CategoryCode and b.departmentName = a.departmentName
            left join 
            (
            select departmentName,sales,cCategoryCode,SUM(iNatSum) as SalesAmount from vw_BG_Bill
            where 1=1 and  cCusCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and left(BudgetMonth,4) = left( '{1}',4)
            group by departmentName,sales,cCategoryCode
            ) c on c.sales = a.sales and c.cCategoryCode =a.CategoryCode and c.departmentName = a.departmentName

            left join
            (
            select departmentName,sales,CategoryCode,SUM(Amount) as BudgetAmount1 from vw_Cms_BG_SalesBudgets_ex
            where 1=1 {0} and BudgetMonth >= '{1}' and BudgetMonth <= '{2}'
            group by departmentName,sales,CategoryCode
            ) b1 on a.sales = b1.sales and a.CategoryCode = b1.CategoryCode and b1.departmentName = a.departmentName
            left join 
            (
            select departmentName,sales,cCategoryCode,SUM(iNatSum) as SalesAmount1 from vw_BG_Bill
            where 1=1 and  cCusCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BudgetMonth >= '{1}' and BudgetMonth <= '{2}'
            group by departmentName,sales,cCategoryCode
            ) c1 on c1.sales = a.sales and c1.cCategoryCode =a.CategoryCode and c1.departmentName = a.departmentName";
            sql = string.Format(sql, sqlwhere, beginMonth, endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }

        //预算达成-按产品
        public DataSet SalesRpt_109(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select cate.FactoryName,cate.categoryNameex,a.DepartmentName, a.sales,isnull(b.BudgetAmount,0) as BudgetAmount,isnull(c.SalesAmount,0) as SalesAmount 
           ,case when isnull(b.BudgetAmount,0)=0 and isnull(c.SalesAmount,0)!=0 then 1 when isnull(b.BudgetAmount,0)=0 then 0 else ROUND(isnull(c.SalesAmount,0) / b.BudgetAmount,4) end as YearRate
            ,isnull(b1.BudgetAmount1,0) as BudgetAmount1,isnull(c1.SalesAmount1,0) as SalesAmount1
            ,case when isnull(b1.BudgetAmount1,0)=0 and isnull(c1.SalesAmount1,0)!=0 then 1 when isnull(b1.BudgetAmount1,0)=0 then 0 else ROUND(isnull(c1.SalesAmount1,0) / b1.BudgetAmount1,4) end as CurRate
            from
            (
	            select sales,CategoryCode,DepartmentName
            from vw_SaleBuget_SalesCategoryCode with(nolock)
            ) a
            --left join vw_sysUser u on u.UserName = a.sales
            left join Cms_Ctrt_InstrumentCategory cate with(nolock) on cate.categoryCode = a.CategoryCode
            left join
            (
            select departmentName,sales,CategoryCode,SUM(Amount) as BudgetAmount from vw_Cms_BG_SalesBudgets_ex
            where 1=1 {0} and left(BudgetMonth,4) = left( '{1}',4)
            group by departmentName,sales,CategoryCode
            ) b on a.sales = b.sales and a.CategoryCode = b.CategoryCode and b.departmentName = a.departmentName
            left join 
            (
            select departmentName,sales,cCategoryCode,SUM(iNatSum) as SalesAmount from vw_BG_Bill
            where 1=1 and  cCusCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and left(BudgetMonth,4) = left( '{1}',4)
            group by departmentName,sales,cCategoryCode
            ) c on c.sales = a.sales and c.cCategoryCode =a.CategoryCode and c.departmentName = a.departmentName

            left join
            (
            select departmentName,sales,CategoryCode,SUM(Amount) as BudgetAmount1 from vw_Cms_BG_SalesBudgets_ex
            where 1=1 {0} and BudgetMonth >= '{1}' and BudgetMonth <= '{2}'
            group by departmentName,sales,CategoryCode
            ) b1 on a.sales = b1.sales and a.CategoryCode = b1.CategoryCode and b1.departmentName = a.departmentName
            left join 
            (
            select departmentName,sales,cCategoryCode,SUM(iNatSum) as SalesAmount1 from vw_BG_Bill
            where 1=1 and  cCusCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BudgetMonth >= '{1}' and BudgetMonth <= '{2}'
            group by departmentName,sales,cCategoryCode
            ) c1 on c1.sales = a.sales and c1.cCategoryCode =a.CategoryCode and c1.departmentName = a.departmentName";
            sql = string.Format(sql, sqlwhere, beginMonth, endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }

        //销售明细统计-按产品线
        public DataTable SaleBillsStatByCategory(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().SaleBillsStat(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);
        }
        //销售方式-产品
        public DataSet SalesRpt_108(string sqlwhere, string beginMonth, string endMonth)
        {
            string sql = @"select FactoryName,categorynameex
                 ,sum(isnull(sale.rdSaleAmount1,0)) as rdSaleAmount1,sum(isnull(sale.rdSaleAmount2,0)) as rdSaleAmount2,sum(isnull(sale.rdSaleAmount3,0)) as rdSaleAmount3,sum(isnull(sale.rdSaleAmount,0)) as rdSaleAmount
                 ,sum(isnull(sale.kxSaleAmount1,0)) as kxSaleAmount1,sum(isnull(sale.kxSaleAmount2,0)) as kxSaleAmount2,sum(isnull(sale.kxSaleAmount3,0)) as kxSaleAmount3,sum(isnull(sale.kxSaleAmount,0)) as kxSaleAmount
                 ,sum(isnull(sale.yxSaleAmount1,0)) as yxSaleAmount1,sum(isnull(sale.yxSaleAmount2,0)) as yxSaleAmount2,sum(isnull(sale.yxSaleAmount3,0)) as yxSaleAmount3,sum(isnull(sale.yxSaleAmount,0)) as yxSaleAmount
                 ,sum(isnull(sale.hzSaleAmount1,0)) as hzSaleAmount1,sum(isnull(sale.hzSaleAmount2,0)) as hzSaleAmount2,sum(isnull(sale.hzSaleAmount3,0)) as hzSaleAmount3,sum(isnull(sale.hzSaleAmount,0)) as hzSaleAmount
                 ,sum(isnull(sale.hcSaleAmount1,0)) as hcSaleAmount1,sum(isnull(sale.hcSaleAmount2,0)) as hcSaleAmount2,sum(isnull(sale.hcSaleAmount3,0)) as hcSaleAmount3,sum(isnull(sale.hcSaleAmount,0)) as hcSaleAmount
                 ,sum(isnull(sale.rdSaleAmount1,0))+sum(isnull(sale.kxSaleAmount1,0))+sum(isnull(sale.yxSaleAmount1,0))+sum(isnull(sale.hzSaleAmount1,0))+sum(isnull(sale.hcSaleAmount1,0)) as TotalSaleAmount1
                 ,sum(isnull(sale.rdSaleAmount2,0))+sum(isnull(sale.kxSaleAmount2,0))+sum(isnull(sale.yxSaleAmount2,0))+sum(isnull(sale.hzSaleAmount2,0))+sum(isnull(sale.hcSaleAmount2,0)) as TotalSaleAmount2
                 ,sum(isnull(sale.rdSaleAmount3,0))+sum(isnull(sale.kxSaleAmount3,0))+sum(isnull(sale.yxSaleAmount3,0))+sum(isnull(sale.hzSaleAmount3,0))+sum(isnull(sale.hcSaleAmount3,0)) as TotalSaleAmount3
                 ,sum(isnull(sale.rdSaleAmount,0))+sum(isnull(sale.kxSaleAmount,0))+sum(isnull(sale.yxSaleAmount,0))+sum(isnull(sale.hzSaleAmount,0))+sum(isnull(sale.hcSaleAmount,0)) as TotalSaleAmount
                from vw_InstrumentCategory_Stat cc
                left join
                (	
	                select categorycode
	                ,SUM(rdSaleAmount1) as rdSaleAmount1
	                ,SUM(rdSaleAmount2) as rdSaleAmount2
	                ,SUM(rdSaleAmount3) as rdSaleAmount3
	                ,SUM(rdSaleAmount1)+SUM(rdSaleAmount2)+SUM(rdSaleAmount3) as rdSaleAmount
	                ,SUM(kxSaleAmount1) as kxSaleAmount1
	                ,SUM(kxSaleAmount2) as kxSaleAmount2
	                ,SUM(kxSaleAmount3) as kxSaleAmount3
	                ,SUM(kxSaleAmount1)+SUM(kxSaleAmount2)+SUM(kxSaleAmount3) as kxSaleAmount
	                ,SUM(yxSaleAmount1) as yxSaleAmount1
	                ,SUM(yxSaleAmount2) as yxSaleAmount2
	                ,SUM(yxSaleAmount3) as yxSaleAmount3
	                ,SUM(yxSaleAmount1)+SUM(yxSaleAmount2)+SUM(yxSaleAmount3) as yxSaleAmount
	                ,SUM(hzSaleAmount1) as hzSaleAmount1
	                ,SUM(hzSaleAmount2) as hzSaleAmount2
	                ,SUM(hzSaleAmount3) as hzSaleAmount3
	                ,SUM(hzSaleAmount1)+SUM(hzSaleAmount2)+SUM(hzSaleAmount3) as hzSaleAmount
	                ,SUM(hcSaleAmount1) as hcSaleAmount1
	                ,SUM(hcSaleAmount2) as hcSaleAmount2
	                ,SUM(hcSaleAmount3) as hcSaleAmount3
	                ,SUM(hcSaleAmount1)+SUM(hcSaleAmount2)+SUM(hcSaleAmount3) as hcSaleAmount
	                
	                from
	                (
	                select categorycode,BillMonth,CustomerCode,companyname,sales
	                ,case when companyName='润达' and SalesModelType = '直销' then iNatSum else 0 end as rdSaleAmount1
	                ,case when companyName='润达' and SalesModelType = '经销' then iNatSum else 0 end as rdSaleAmount2
	                ,case when companyName='润达' and SalesModelType = '三产' then iNatSum else 0 end as rdSaleAmount3
	                ,case when companyName='康祥' and SalesModelType = '直销' then iNatSum else 0 end as kxSaleAmount1
	                ,case when companyName='康祥' and SalesModelType = '经销' then iNatSum else 0 end as kxSaleAmount2
	                ,case when companyName='康祥' and SalesModelType = '三产' then iNatSum else 0 end as kxSaleAmount3
	                ,case when companyName='益信' and SalesModelType = '直销' then iNatSum else 0 end as yxSaleAmount1
	                ,case when companyName='益信' and SalesModelType = '经销' then iNatSum else 0 end as yxSaleAmount2
	                ,case when companyName='益信' and SalesModelType = '三产' then iNatSum else 0 end as yxSaleAmount3
	                ,case when companyName='惠中' and SalesModelType = '直销' then iNatSum else 0 end as hzSaleAmount1
	                ,case when companyName='惠中' and SalesModelType = '经销' then iNatSum else 0 end as hzSaleAmount2
	                ,case when companyName='惠中' and SalesModelType = '三产' then iNatSum else 0 end as hzSaleAmount3
	                ,case when companyName='华臣' and SalesModelType = '直销' then iNatSum else 0 end as hcSaleAmount1
	                ,case when companyName='华臣' and SalesModelType = '经销' then iNatSum else 0 end as hcSaleAmount2
	                ,case when companyName='华臣' and SalesModelType = '三产' then iNatSum else 0 end as hcSaleAmount3
	                from vw_BG_Bill) s
                    where CustomerCode not in(select customerCode from cms_Bg_InnerCustomer) {0} and BillMonth >= '{1}' and BillMonth <= '{2}'
	                group by categorycode
                ) sale on sale.categorycode = cc.categoryCode
                group by FactoryName,categorynameex order by FactoryName,categorynameex";


            sql = string.Format(sql, sqlwhere, beginMonth, endMonth);
            return new CommonDal().getSqlData_DS(sql);
        }

        //销售收入、成本
        public DataTable GetSaleRevenue(string WhereClause)
        {
            return new CtrtBillDal().GetSaleRevenue(WhereClause);
        }
        //销售明细统计 金额汇总
        public DataTable CalcSaleBillsAmount(string WhereClause)
        {
            return new CtrtBillDal().CalcSaleBillsAmount(WhereClause);
        }
        public DataTable CalcSaleBillsAmount1(string WhereClause)
        {
            return new CtrtBillDal().CalcSaleBillsAmount1(WhereClause);
        }
        //无法与预算匹配的销售明细
        public DataTable SaleBillNotinBudget(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().SaleBillNotinBudget(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);
        }

        //有预算无销售
        public DataTable BudgetNotSaleBill(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().BudgetNotSaleBill(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);
        }
        //无法与预算匹配的销售明细的总金额
        public DataTable CalcbillAmount(string WhereClause)
        {
            return new CtrtBillDal().CalcbillAmount(WhereClause);
        }

        //有预算无销售的预算总额
        public DataTable CalcBudgetAmount(string WhereClause)
        {
            return new CtrtBillDal().CalcBudgetAmount(WhereClause);
        }

        //按客户奖惩汇总
        public DataSet ARRpt_01(string sqlwhere)
        {
            string sql = @"select * from (
            select a.cCuscode,a.cBVCode,a.dDate,alerttype,a.iNatSum,a.iExchSum,isnull(b.Fine,0) as Fine,isnull(b.Reward,0) as Reward,b.Year,b.Month,cast(b.Year as varchar(4))+'-'+right('00'+cast(b.Month as varchar(2)),2) as Yearmonth,a.AccountPayable as AccountPayable from 
            (select cCuscode,cBVCode,dDate,alerttype,sum(iNatSum) as iNatSum,sum(iExchSum) as iExchSum,sum(iNatSum-iExchSum) as AccountPayable from vw_Invoice  where 1=1  group by cCuscode,cBVCode,dDate,alerttype) a 
            left join Cms_Arm_UserPerformance b
            on a.cBVCode = b.cBVCode
            ) AS Z where 1=1 {0} order by dDate desc";


            sql = string.Format(sql, sqlwhere);
            return new CommonDal().getSqlData_DS(sql);
        }


        public DataTable GetCustomerPerformance(string WhereClause, Guid UserID, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().GetCustomerPerformance(WhereClause, UserID, OrderBy, PageSize, PageIndex, out RecordCount);
        }
        public DataTable GetCustomerPerformance(string WhereClause, string UFaccountno, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().GetCustomerPerformance(WhereClause, UFaccountno, OrderBy, PageSize, PageIndex, out RecordCount);
        }
        public DataTable CalcCustomerPerformance(string WhereClause, Guid UserID)
        {
            return new CtrtBillDal().CalcCustomerPerformance(WhereClause, UserID);
        }
        public DataTable CalcCustomerPerformance(string WhereClause, string UFaccountno)
        {
            return new CtrtBillDal().CalcCustomerPerformance(WhereClause, UFaccountno);
        }

        public DataTable GetCustomerAgeStat(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().GetCustomerAgeStat(WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);
        }

        public DataTable GetBillPerformance(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().GetBillPerformance(WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);
        }

        public DataTable QueryUserPerformance(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QueryUserPerformance(WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);
        }

        public DataTable QueryUserPerformance(string strAccountno ,string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QueryUserPerformance(strAccountno,WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);
        }

        public DataTable CalcUserPerformance(string WhereClause)
        {
            return new CtrtBillDal().CalcUserPerformance(WhereClause);
        }

        public DataTable getCheckOffsListByMonthAndYear(string WhereClause, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().getCheckOffsListByMonthAndYear(WhereClause,PageSize,PageIndex,out RecordCount);
        }

        public DataTable QueryUserPerformanceSummary(string WhereClause,  string OrderBy,int PageSize, int PageIndex, out int RecordCount) {
            return new CtrtBillDal().QueryUserPerformanceSummary(WhereClause, OrderBy, PageSize, PageIndex, out RecordCount);
        }

        public int updateBillDueDate(string BVCode, string Duedate)
        {
            return new CtrtBillDal().updateBill(BVCode, Duedate);
        }

        public int Executesql(string sql)
        {
            return new CtrtBillDal().Executesql(sql);
        }

        //获得存货明细
        public DataTable QueryInstrumentList(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QueryInstrumentList(WhereClause, orderBy, PageSize, PageIndex, out RecordCount);

        }
        //获得存货明细
        public DataTable QueryInstrumentList(string WhereClause,string OrderBy)
        {
            return new CtrtBillDal().QueryInstrumentList(WhereClause, OrderBy);
        }

        //获得分公司初始的存货价格
        public DataTable QueryCompanyInstrumentCostList(string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QueryCompanyInstrumentCostList( WhereClause, orderBy, PageSize, PageIndex, out RecordCount);

        }

        //获得存货价格
        public DataTable QueryInstrumentCostList(string CostYear,string WhereClause, string orderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            return new CtrtBillDal().QueryInstrumentCostList(CostYear,WhereClause, orderBy, PageSize, PageIndex, out RecordCount);

        }

        #region 导出
        //销售员对应客户的应收款和奖惩情况
        public DataTable ExportMyCustomerPerformance(string WhereClause, Guid UserID)
        {
            return new CtrtBillDal().GetCustomerPerformance_export(WhereClause, UserID);
        }
        //所有客户的应收款和奖惩情况
        public DataTable ExportAllCustomerPerformance(string WhereClause,string ufAccountno)
        {
            return new CtrtBillDal().GetCustomerPerformance_export(WhereClause, ufAccountno);
        }
        //客户名下的发票 
        public DataTable ExportCustomerBills(string WhereClause, string Orderby)
        {
            return new CtrtBillDal().GetBillPerformance_export(WhereClause, Orderby);
        }

        //客户名下的发票 
        public DataTable ExportCustomerAgeStat(string WhereClause)
        {
            return new CtrtBillDal().ExportCustomerAgeStat(WhereClause);
        }
        //销售员的所有发票应收款和奖惩情况
        public DataTable ExportUserPerformance(string WhereClause, string OrderBy)
        {
            return new CtrtBillDal().QueryUserPerformance_export(WhereClause, OrderBy);
        }
        //销售员的奖惩核销
        public DataTable ExportUserCheckOff(string WhereClause, int iyear,int imonth)
        {
            return new CtrtBillDal().QueryUserCheckOff_export(WhereClause, iyear, imonth);
        }

        //销售明细统计导出
        public DataTable ExportSaleBillsStat(string WhereClause)
        {
            return new CtrtBillDal().SaleBillsStat_export(WhereClause);
        }
        //销售明细统计导出
        public DataTable ExportSaleBillsStat1(string WhereClause)
        {
            return new CtrtBillDal().SaleBillsStat1_export(WhereClause);
        }
        public DataTable ExportSaleBills(string WhereClause)
        {
            return new CtrtBillDal().SalesBills_export(WhereClause);
        }

        public DataTable BGInstrument_export(string WhereClause)
        {
            return new CtrtBillDal().BGInstrument_export(WhereClause);
        }
        

        //判断核销时输入的发票是否有效
        public int IsExistsInv(string invNO)
        {
            return new CtrtBillDal().IsExistsInv(invNO);
        }

        //判断预算单位已关联的销售记录
        public int AssociatedSaleUnitnum(string sqlWhere)
        {
            return new CtrtBillDal().AssociatedSaleUnitnum(sqlWhere);
        }

        //获取预算单位已关联的销售记录
        public DataTable AssociatedSaleUnit(string sqlWhere)
        {
            return new CtrtBillDal().AssociatedSaleUnit(sqlWhere);
        }

        public DataTable getAccountStatusList(string whereSql)
        {
            string sql = @"select * from cms_sys_CloseAccount where 1=1 {0} order by iMonth asc";
            string strsql = string.Format(sql, whereSql);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(strsql);

        }

        public DataTable getRevenueCloseList(string whereSql)
        {
            string sql = @"select a.*
                    ,(b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) as SaleAmount
                    ,(b.Cost+b.Cost3+b.Cost2) as Cost 
                    ,b.SaleAmount as SaleAmount0
                    ,b.Cost as Cost0 
                    ,((b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) - (b.Cost+b.Cost3+b.Cost2)) as Revenue
                    ,case when (b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) <>0 then ((b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) - (b.Cost+b.Cost3+b.Cost2))/ (b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) else 0 end as RevenueRate
                    from cms_Sys_CheckRevenue a
                    inner join vw_InstrumentRevenue b on a.iYear = b.billYear and a.iMonth = b.billMonth where 1=1 {0} order by iMonth asc";
            string strsql = string.Format(sql, whereSql);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(strsql);

        }

        public DataTable CalcRevenue(string whereSql)
        {
            string sql = @"select sum(a.RevenueByManual) as RevenueByManual,sum(a.CostByManual) as CostByManual
                    ,sum(b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) as SaleAmount
                    ,sum((b.Cost+b.Cost3+b.Cost2)) as Cost 
                    ,sum(((b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) - (b.Cost+b.Cost3+b.Cost2))) as Revenue
                    ,case when sum(b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) <>0 then (sum(b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) - sum(b.Cost+b.Cost3+b.Cost2))/ sum(b.SaleAmount+b.SaleAmount1+b.SaleAmount2+b.SaleAmount3+b.SaleAmount4) else 0 end as RevenueRate
                    from cms_Sys_CheckRevenue a
                    inner join vw_InstrumentRevenue b on a.iYear = b.billYear and a.iMonth = b.billMonth where 1=1 {0}";
            string strsql = string.Format(sql, whereSql);
            BGSalesBudgetsDal dal = new BGSalesBudgetsDal();
            return new CommonBll().getDataFromSQL(strsql);

        }

        #endregion

        public string UpdateInstrumentCost(int iYear, int iMonth)
        {
            return new CtrtBillDal().UpdateInstrumentCost(iYear, iMonth);
        }

        public string settleAccount(int iYear, int iMonth)
        {
            return new CtrtBillDal().settleAccount(iYear, iMonth);
        }


        public int cancelApportion(int iYear, int iMonth)
        {
            return new CtrtBillDal().cancelApportion(iYear, iMonth);
        }


        public string CheckBalance(Guid CheckID, decimal RevenueByManual, decimal CostByManual)
        {
            return new CtrtBillDal().CheckBalance(CheckID, RevenueByManual, CostByManual);
        }

        //确认存货档案信息时，更新发票中的存货大类（主要考虑到益信手工导入发票时，可能会发现发票中存货大类编号有误）
        public int updateBillCategoryCode(string sCompanyname ,string sInstrumentCode,string sCategoryCode)
        {
            return new CtrtBillDal().updateBillCategoryCode(sCompanyname, sInstrumentCode, sCategoryCode);
        }
        public int updateCheckStatus(int iYear, int iMonth, string sStatus)
        {
            return new CtrtBillDal().updateCheckStatus(iYear, iMonth, sStatus);
        }

        public int updatesettleStatus(int iYear, int iMonth, string sStatus)
        {
            return new CtrtBillDal().updatesettleStatus(iYear, iMonth, sStatus);
        }

        public int updateApportion1Status(int iYear, int iMonth,string sStatus)
        {
            return new CtrtBillDal().updateApportion1Status(iYear, iMonth, sStatus);
        }
        public int updateApportion2Status(int iYear, int iMonth, string sStatus)
        {
            return new CtrtBillDal().updateApportion2Status(iYear, iMonth, sStatus);
        }
        public int updateApportion3Status(int iYear, int iMonth, string sStatus)
        {
            return new CtrtBillDal().updateApportion3Status(iYear, iMonth, sStatus);
        }
        

        public string UpdateRptData(Guid CheckID, int iYear, int iMonth, decimal RevenueByManual, decimal CostByManual, decimal SaleAmount, decimal Cost)
        {
            return new CtrtBillDal().UpdateRptData(CheckID, iYear, iMonth, RevenueByManual, CostByManual, SaleAmount, Cost);
        }

        public void checkRptData(int iYear, int iMonth)
        {
            new CtrtBillDal().checkRptData(iYear, iMonth);
        }
    }
}
